package com.example.create_excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@RestController
public class ExcelDownloadController {
    private final DataService dataService;
    private final Map<String, HSSFCellStyle> contextStyle;
    private final String[] keys;
    private final String[] header;
    private final int[] width;

    @Autowired
    public ExcelDownloadController(DataService dataService) {
        this.dataService = dataService;
        this.contextStyle = new HashMap<>();
        keys = new String[]{"NO", "COMPANYNAME", "DIRNUMBER", "DIRNAME", "UPDATETIME", "NOTUPDATEDAY", "MONTHLYDATANUMBER", "SUMDATANUMBER", "AVENOTUPDATEDAY"};
        header = new String[]{"序号", "单位名称", "目录数", "目录名", "最新归集时间", "数据未更新时间（天）", "月数据归集条数", "累计数据归集条数", "数据平均未更新天数"};
        width = new int[]{5, 14, 8, 53, 18, 21, 18, 18, 20};
    }

    @GetMapping("/ExcelDownload")
    public void excelDownload(HttpServletResponse response) throws IOException, ParseException {
        List<Map<String, String>> context_ywlj = dataService.getData_ywlj();
        List<Map<String, String>> context_fgj = dataService.getData_fgj();
        HSSFWorkbook workbook = new HSSFWorkbook();
        setContextStyle(workbook);
        HSSFSheet sheet = workbook.createSheet("一委两局");
        HSSFSheet sheet_2 = workbook.createSheet("房管局");
        //添加表头
        addHeader(workbook, sheet);
        addHeader(workbook, sheet_2);
        //添加正文
        addContext(sheet, context_ywlj);
        addContext(sheet_2, context_fgj);
        response.setContentType("multipart/form-data");
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(("数据归集时间统计" + df.format(new Date()) + "(委办公室).xls").getBytes(StandardCharsets.UTF_8), "ISO8859-1"));
        response.flushBuffer();
        OutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
        out.close();
    }

    private void addHeader(HSSFWorkbook workbook, HSSFSheet sheet) {
        //设置标题样式
        sheet.setAutoFilter(CellRangeAddress.valueOf("A1:I1"));
        sheet.createFreezePane(0, 1);
        //添加标题
        HSSFRow headRow = sheet.createRow(0);
        for (int i = 0; i < header.length; i++) {
            sheet.setColumnWidth(i, width[i] * 256);
            HSSFCell cell = headRow.createCell(i);
            cell.setCellValue(header[i]);
            cell.setCellStyle(getHeaderStyle(workbook));
        }
    }

    private HSSFCellStyle getHeaderStyle(HSSFWorkbook workbook) {
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        HSSFPalette palette = workbook.getCustomPalette();
        palette.setColorAtIndex(IndexedColors.ROYAL_BLUE.getIndex(), (byte) 79, (byte) 129, (byte) 189);
        headerStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置标题字体
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        font.setBold(true);
        font.setColor(IndexedColors.WHITE.getIndex());
        headerStyle.setFont(font);
        //headerStyle.setWrapText(true);
        return headerStyle;
    }

    private void addContext(HSSFSheet sheet, List<Map<String, String>> context) throws ParseException {
        String lastName = "";
        int index = 0;
        for (Map<String, String> stringStringMap : context) {
            ++index;
            HSSFRow contextRow = sheet.createRow(index);
            for (int j = 0; j < keys.length; ++j) {
                HSSFCell cell = contextRow.createCell(j);
                String text = stringStringMap.get(keys[j]);
                setValue(cell, keys[j], text);
            }
            String temp = stringStringMap.get("COMPANYNAME");
            int num = Integer.parseInt(stringStringMap.get("DIRNUMBER"));
            if (!Objects.equals(temp, lastName) && num != 1) {
                lastName = temp;
                sheet.addMergedRegion(new CellRangeAddress(index, index + num - 1, 1, 1));
                sheet.addMergedRegion(new CellRangeAddress(index, index + num - 1, 2, 2));
                sheet.addMergedRegion(new CellRangeAddress(index, index + num - 1, 8, 8));
            }
        }
    }

    private void setValue(HSSFCell cell, String column, String text) throws ParseException {
        if (text != null && !Objects.equals(text, "") && !Objects.equals(text, "-"))
            switch (column) {
                case "NO":
                case "DIRNUMBER":
                    cell.setCellValue(Integer.parseInt(text));
                    break;
                case "COMPANYNAME":
                case "DIRNAME":
                case "MONTHLYDATANUMBER":
                case "SUMDATANUMBER":
                    cell.setCellValue(text);
                    break;
                case "UPDATETIME":
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    SimpleDateFormat sdf_1 = new SimpleDateFormat("yyyy/M/d HH:mm");
                    Calendar cal = Calendar.getInstance();
                    cal.setTime(sdf.parse(text));
                    cell.setCellValue(sdf_1.format(cal.getTime()));
                    break;
                case "NOTUPDATEDAY":
                case "AVENOTUPDATEDAY":
                    cell.setCellValue(Double.parseDouble(text));
                    break;
            }
        cell.setCellStyle(contextStyle.get(column));
    }

    private void setContextStyle(HSSFWorkbook workbook) {
        for (String key : keys) {
            //设置正文样式
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置正文字体
            HSSFFont contextFont = workbook.createFont();
            contextFont.setFontName("宋体");
            contextFont.setFontHeightInPoints((short) 11);
            cellStyle.setFont(contextFont);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            HSSFDataFormat format = workbook.createDataFormat();
            switch (key) {
                case "NO":
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    contextStyle.put("NO", cellStyle);
                    break;
                case "DIRNUMBER":
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    contextStyle.put("DIRNUMBER", cellStyle);
                    break;
                case "COMPANYNAME":
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    contextStyle.put("COMPANYNAME", cellStyle);
                    break;
                case "DIRNAME":
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    contextStyle.put("DIRNAME", cellStyle);
                    break;
                case "MONTHLYDATANUMBER":
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    contextStyle.put("MONTHLYDATANUMBER", cellStyle);
                    break;
                case "SUMDATANUMBER":
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    contextStyle.put("SUMDATANUMBER", cellStyle);
                    break;
                case "UPDATETIME":
                    cellStyle.setAlignment(HorizontalAlignment.LEFT);
                    contextStyle.put("UPDATETIME", cellStyle);
                    break;
                case "NOTUPDATEDAY":
                    cellStyle.setAlignment(HorizontalAlignment.RIGHT);
                    cellStyle.setDataFormat(format.getFormat("0"));
                    contextStyle.put("NOTUPDATEDAY", cellStyle);
                    break;
                case "AVENOTUPDATEDAY":
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setDataFormat(format.getFormat("0.00"));
                    contextStyle.put("AVENOTUPDATEDAY", cellStyle);
                    break;
            }
        }
    }
}
